package com.demo.ohos_zblibrary.manager;

import ohos.app.Context;
import ohos.data.DatabaseHelper;
import ohos.data.rdb.*;
import ohos.data.resultset.ResultSet;
import zuo.biao.library.util.Log;
import zuo.biao.library.util.StringUtil;

import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.Set;

/**SQLite数据库Helper
 * @author Lemon
 */
public class SQLHelper {
    private static final String TAG = "SQLHelper";

    /**
     * 版本号
     */
    public static final int TABLE_VERSION = 1;

    /**
     * 表名称
     */
    public static final String TABLE_NAME = "zblibrary_demo";

    /**
     * 表id
     */
    public static final String COLUMN_ID = "_id"; // long类型的id不能自增，primary key autoincrement会出错

    /**
     * 表name
     */
    public static final String COLUMN_NAME = "name";

    /**
     * 表phone
     */
    public static final String COLUMN_PHONE = "phone";
    /**
     * 表mail
     */
    public static final String COLUMN_MAIL = "mail";
    /**
     * 表other
     */
    public static final String COLUMN_OTHER = "other";
    private Context context;
    private RdbStore db;

    private static RdbOpenCallback callback =
            new RdbOpenCallback() {
                @Override
                public void onCreate(RdbStore store) {
                    String sql =
                            "CREATE TABLE "
                                    + TABLE_NAME
                                    + " ("
                                    + COLUMN_ID
                                    + " INTEGER primary key autoincrement, "
                                    + COLUMN_NAME
                                    + " text, "
                                    + COLUMN_PHONE
                                    + " text, "
                                    + COLUMN_MAIL
                                    + " text, "
                                    + COLUMN_OTHER
                                    + " text)";
                    store.executeSql(sql);
                }

                @Override
                public void onUpgrade(RdbStore store, int oldVersion, int newVersion) {
                    store.executeSql("DROP TABLE IF EXISTS " + TABLE_NAME);
                    onCreate(store);
                }
            };

    public SQLHelper(Context context) {
        this.context = context;

        StoreConfig config = StoreConfig.newDefaultConfig(TABLE_NAME);
        DatabaseHelper helper = new DatabaseHelper(context);
        db = helper.getRdbStore(config, 1, callback, null);
    }

    /**
     * 根据Cursor获取内容
     * @param cursor c
     * @return ValuesBucket
     */
    public ValuesBucket getValue(ResultSet cursor) {
        ValuesBucket values = null;
        if (cursor != null) {
            values = new ValuesBucket();

            if (cursor.goToNextRow()) {
                values.putString(COLUMN_ID, cursor.getString(cursor.getColumnIndexForName(COLUMN_ID)));
                values.putString(COLUMN_NAME, cursor.getString(cursor.getColumnIndexForName(COLUMN_NAME)));
                values.putString(COLUMN_PHONE, cursor.getString(cursor.getColumnIndexForName(COLUMN_PHONE)));
                values.putString(COLUMN_MAIL, cursor.getString(cursor.getColumnIndexForName(COLUMN_MAIL)));
                values.putString(COLUMN_OTHER, cursor.getString(cursor.getColumnIndexForName(COLUMN_OTHER)));
            }
            cursor.close();
        }
        return values;
    }

    /**
     * 根据Cursor获取内容
     * @param cursor c
     * @return List<ValuesBucket>
     */
    public List<ValuesBucket> getValueList(ResultSet cursor) {
        List<ValuesBucket> list = null;
        if (cursor != null) {
            list = new ArrayList<ValuesBucket>();

            ValuesBucket values;
            while (cursor.goToNextRow()) {
                values = new ValuesBucket();
                values.putString(COLUMN_ID, cursor.getString(cursor.getColumnIndexForName(COLUMN_ID)));
                values.putString(COLUMN_NAME, cursor.getString(cursor.getColumnIndexForName(COLUMN_NAME)));
                values.putString(COLUMN_PHONE, cursor.getString(cursor.getColumnIndexForName(COLUMN_PHONE)));
                values.putString(COLUMN_MAIL, cursor.getString(cursor.getColumnIndexForName(COLUMN_MAIL)));
                values.putString(COLUMN_OTHER, cursor.getString(cursor.getColumnIndexForName(COLUMN_OTHER)));

                list.add(values);
            }
            cursor.close();
        }
        return list;
    }

    /**获取正确的的ContentValues，防止数据库操作出错
     * @param values v
     * @return ValuesBucket
     */
    public ValuesBucket getCorrectValues(ValuesBucket values) {
        if (values == null || values.size() <= 0) {
            return  Optional.ofNullable(values).get();
        }

        // 去除所有空key
        Set<String> set = values.getColumnSet();
        for (String key : set) {
            if (StringUtil.isNotEmpty(key, true) == false) {
                values.delete(key);
            }
        }

        return values;
    }

    /**
     * 插入数据
     * @param id id
     * @param values - 一行键值对数据
     */
    public void put(int id, ValuesBucket values) {
        put(COLUMN_ID, "" + id, values);
    }

    /**
     * 插入数据
     * @param column - 列名称
     * @param value - 筛选数据的条件值
     * @param values - 一行键值对数据
     */
    public void put(String column, String value, ValuesBucket values) {
        ValuesBucket oldValues = get(column, value);
        if (oldValues != null
                && oldValues.hasColumn(COLUMN_ID)
                && StringUtil.isNotEmpty(oldValues.getString(COLUMN_ID), true)) { // 数据存在且有效
            update(column, value, values);
        } else {
            insert(values);
        }
    }

    /**
     * 获取单个数据
     * @param id id
     * @return ValuesBucket
     */
    public ValuesBucket get(int id) {
        return getValue(query(id));
    }

    /**
     * 获取单个数据
     * @param column column
     * @param value value
     * @return ValuesBucket
     */
    public ValuesBucket get(String column, String value) {
        return getValue(query(column, value));
    }

    /**
     * 获
     * 取数据列表
     * @param column column
     * @param value v
     * @return List<ValuesBucket>
     */
    public List<ValuesBucket> getList(String column, String value) {
        return getValueList(query(column, value));
    }

    /**
     * 获取所有数据
     * @return List<ValuesBucket>
     */
    public List<ValuesBucket> getAll() {
        return getList(null, null);
    }

    /**
     * 插入数据
     * @param values v
     * @return long
     */
    public long insert(ValuesBucket values) {
        try {
            return db.insert(TABLE_NAME, getCorrectValues(values));
        } catch (Exception e) {
            Log.error(TAG, "update   try { return db.insert(.... } catch (Exception e) {\n " + e.getMessage());
        }
        return -1;
    }

    /**
     * 更新数据
     * @param id id
     * @param values v
     * @return int
     */
    public int update(int id, ValuesBucket values) {
        return update(COLUMN_ID, "" + id, values);
    }

    /**
     * 更新数据
     * @param column column
     * @param value v
     * @param values values
     * @return 0
     */
    public int update(String column, String value, ValuesBucket values) {
        try {
            RdbPredicates predicates = new RdbPredicates(TABLE_NAME);
            predicates.equalTo(getSelection(column), value);

            return db.update(getCorrectValues(values), predicates);
        } catch (Exception e) {
            Log.error(TAG, "update   try { return db.update(.... } catch (Exception e) {\n " + e.getMessage());
        }
        return 0;
    }

    /**
     * 删除数据
     * @param id id
     * @return int
     */
    public int delete(int id) {
        return delete(COLUMN_ID, "" + id);
    }

    /**
     * 删除数据
     * @param column column
     * @param value v
     * @return int
     */
    public int delete(String column, String value) {
        try {
            RdbPredicates predicates = new RdbPredicates(TABLE_NAME);
            predicates.equalTo(getSelection(column), value);
            return db.delete(predicates);
        } catch (Exception e) {
            Log.error(TAG, "update   try { return db.delete(.... } catch (Exception e) {\n " + e.getMessage());
        }
        return 0;
    }

    /**
     * 查询所有数据
     * @return ResultSet
     */
    public ResultSet queryAll() {
        try {
            RdbPredicates predicates = new RdbPredicates(TABLE_NAME);
            String[] columns = new String[] {COLUMN_ID, COLUMN_NAME, COLUMN_PHONE, COLUMN_MAIL, COLUMN_OTHER};
            return db.query(predicates, columns);
        } catch (Exception e) {
            Log.error(TAG, "queryAll  try { return db.query(...} catch (Exception e) {" + e.getMessage());
        }
        return null;
    }

    /**
     * 查询单个数据
     * @param id 单个id
     * @return ResultSet
     */
    public ResultSet query(int id) {
        return query(COLUMN_ID, "" + id);
    }

    /**
     * 查询单个数据
     * @param column column
     * @param value value
     * @return ResultSet
     */
    public ResultSet query(String column, String value) {
        try {
            RdbPredicates predicates = new RdbPredicates(TABLE_NAME);
            predicates.equalTo(getSelection(column), value);
            String[] columns = new String[] {COLUMN_ID, COLUMN_NAME, COLUMN_PHONE, COLUMN_MAIL, COLUMN_OTHER};
            return db.query(predicates, columns);
        } catch (Exception e) {
            Log.error(TAG, "query  try { return db.query(...} catch (Exception e) {" + e.getMessage());
        }
        return null;
    }

    /**
     * 获取过滤条件类型
     * @param column column
     * @return StringUtil.isNotEmpty(column, false) ? column + " = ?" : null
     */
    private String getSelection(String column) {
        return StringUtil.isNotEmpty(column, false) ? column + " = ?" : null;
    }

    /**
     * 获取过滤条件值
     * @param column c
     * @param value  v
     * @return StringUtil.isNotEmpty(column, false) ? new String[]{value} : nul
     */
    private String[] getSelectionArgs(String column, String value) {
        return StringUtil.isNotEmpty(column, false) ? new String[] {value} : null;
    }
}
